import pymysql
import tkinter as tk
import tkinter.messagebox
# 打开数据库
db=pymysql.connect(host='localhost',user='root',password='zxcvbnm2580',database='db',charset='utf8')
# 使用cursor()方法获取操作游标
cursor=db.cursor()


def IsExists(id):
    sql="select * from students where id=%s"
    # 使用execute方法执行SQL语句
    cursor.execute(sql,id)
    result=cursor.rowcount
    if result:
        return True
    else:
        return False

def Add(id,name,Clanguage,java,python,Totalscore,average):
    if(IsExists(id)==True):
        print('该学生已存在')
        tk.messagebox.showwarning('提示','该学生已存在')
    else:
        sql="insert into  students values(%s,%s,%s,%s,%s,%s,%s)"
        cursor.execute(sql,(id,name,Clanguage,java,python,Totalscore,average))
        result=cursor.rowcount
        if result:
            print('添加成功')
            tk.messagebox.showinfo('提示','添加成功!')
        else:
            print('添加失败')
            tk.messagebox.showerror('提示','添加失败!')

def Delete(id):
    if(IsExists(id)==True):
        sql="delete from students where id=%s"
        cursor.execute(sql,id)
        result=cursor.rowcount
        if result:
            print('删除成功')
            tk.messagebox.showinfo('提示','删除成功!')
        else:
            print('删除失败')
            tk.messagebox.showerror('提示','删除失败!')
    else:
        print('该学生不存在')
        tk.messagebox.showwarning('提示','该学生不存在!')



def Update(id,name,Clanguage,java,python,Totalscore,average):
    if(IsExists(id)==True):
        sql='update students set name=%s,Clanguage=%s,java=%s,python=%s,Totalscore=%s,average=%s where id=%s'
        cursor.execute(sql,(name,Clanguage,java,python,Totalscore,average,id))
        result=cursor.rowcount
        if result:
            print('修改成功')
            tk.messagebox.showinfo('提示','修改成功!')
        else:
            print('修改失败')
            tk.messagebox.showerror('提示','修改失败!')
    else:
        print('未查询到该学生')
        tk.messagebox.showwarning('提示','未查询到该学生!')



def Query(id):
    sql="select * from students where id =%s"
    cursor.execute(sql,id)
    result=cursor.fetchone()
    # print(result)
    return result

def Queryname(name):
    sql="select * from students where name =%s"
    cursor.execute(sql,name)
    result=cursor.fetchone()
    # print(result)
    return result

def findsex(Totalscore):
    sql="select * from students where Totalscore =%s"
    cursor.execute(sql,Totalscore)
    result=cursor.fetchall()
    # print(result)
    return result

# def QueryAll():
#     #list=['学号:','姓名:','C语言:'...:']
#     sql="select * from students"
#     cursor.execute(sql)
#     result=cursor.fetchall()
#     #for items in result:
#        # for index,item in enumerate(items):
#       #      print(list[index],item,end='')
#      #   print('\n')
#     #print(result)
#     return result
#
# def QueryAll2():
#     sql="select * from class"
#     cursor.execute(sql)
#     result=cursor.fetchall()
#     return result

def QueryAll3():
    sql="select * from students"
    cursor.execute(sql)
    result=cursor.fetchall()
    return result

def QueryAll4():
    sql="select * from students order by python desc"
    cursor.execute(sql)
    result=cursor.fetchall()
    return result
def QueryAll5():
    sql="select * from students order by C desc"
    cursor.execute(sql)
    result=cursor.fetchall()
    return result
def QueryAll6():
    sql="select * from students order by java desc"
    cursor.execute(sql)
    result=cursor.fetchall()
    return result
def QueryAll7():
    sql="select * from students order by Totalscore desc"
    cursor.execute(sql)
    result=cursor.fetchall()
    return result
def QueryAll8():
    sql="select * from students order by average desc"
    cursor.execute(sql)
    result=cursor.fetchall()
    return result

QueryAll3()